package com.aep.provisioning.dao;

import java.math.BigDecimal;
import java.util.List;

import com.aep.provisioning.domain.EspRateCode;
import com.aep.provisioning.util.Constants;


public class EspRateCodeDAOImpl extends BaseDAOHibernate implements EspRateCodeDAO{

	//search Standard monthly
	public List<EspRateCode> findListEspRateCodeMonthly(Long utilityID, String plan){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,plan};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id=? ")
		.append("and e.status.typeCode=? ")
		.append("and e.plan.typeCode=? ")
		.append("and e.monthlyRate IS NOT NULL ")
		.append("GROUP BY e.monthlyRate ")
		.append("ORDER BY e.monthlyRate ASC ");
		return super.find(EspRateCode.class, query.toString(), params);
	}
	
	//search Standard kWhRate
	public List<EspRateCode> findStandardERateCodekWhRate(Long utilityID, String plan,BigDecimal monthly){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,plan,monthly};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id=? ")
		.append("and e.status.typeCode=? ")
		.append("and e.plan.typeCode=? ")
		.append("and e.monthlyRate =? ")
		.append("and e.kWhRate IS NOT NULL ")
		.append("GROUP BY e.kWhRate ")
		.append("ORDER BY e.kWhRate ASC ");
		return super.find(EspRateCode.class, query.toString(), params);
	}
	
	//search Standard kWRate
	public List<EspRateCode>findStandardERateCodekWRate(Long utilityID, String plan,BigDecimal monthly,BigDecimal kWhRate){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,plan,monthly,kWhRate};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id=? ")
		.append("and e.status.typeCode=? ")
		.append("and e.plan.typeCode=? ")
		.append("and e.monthlyRate =? ")
		.append("and e.kWhRate =? ")
		.append("and e.kWRate IS NOT NULL ")
		.append("GROUP BY e.kWRate ")
		.append("ORDER BY e.kWRate ASC ");
		return super.find(EspRateCode.class,query.toString(), params);
	}
	
	//search Standard rateCode
	public EspRateCode findStandardERateCode(Long utilityID, String plan,BigDecimal monthly,BigDecimal kWhRate,BigDecimal kWRate){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,plan,monthly,kWhRate,kWRate};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id=? ")
		.append("and e.status.typeCode=? ")
		.append("and e.plan.typeCode=? ")
		.append("and e.monthlyRate =? ")
		.append("and e.kWhRate =? ")
		.append("and e.kWRate =? ")
		.append("and e.rateCode IS NOT NULL ")
		.append("GROUP BY e.rateCode ")
		.append("ORDER BY e.rateCode ASC ");
		return super.findFirst(EspRateCode.class, query.toString(), params);
	}
	
	//search SEASONAL SummerkWh
	public List<EspRateCode> findSeasonalERateCodeSummerkWh(Long utilityID, String plan,BigDecimal monthly){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,plan,monthly};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id=? ")
		.append("and e.status.typeCode=? ")
		.append("and e.plan.typeCode=? ")
		.append("and e.monthlyRate =? ")
		.append("and e.summerKWhRate IS NOT NULL ")
		.append("GROUP BY e.summerKWhRate ")
		.append("ORDER BY e.summerKWhRate ASC ");
		return super.find(EspRateCode.class, query.toString(), params);
	}

	//search SEASONAL NonSummerkWh
	public List<EspRateCode> findSeasonalERateCodeNonSummerkWh(Long utilityID, String plan,BigDecimal monthly,BigDecimal summerKWhRate){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,plan,monthly,summerKWhRate};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id=? ")
		.append("and e.status.typeCode=? ")
		.append("and e.plan.typeCode=? ")
		.append("and e.monthlyRate =? ")
		.append("and e.summerKWhRate =? ")
		.append("and e.nonSummerKWhRate IS NOT NULL ")
		.append("GROUP BY e.nonSummerKWhRate ")
		.append("ORDER BY e.nonSummerKWhRate ASC ");
		return super.find(EspRateCode.class, query.toString(), params);
	}
	
	//search SEASONAL Per KW Seasonal
	public List<EspRateCode> findSeasonalERateCodekW(Long utilityID, String plan,BigDecimal monthly,BigDecimal summerKWhRate,BigDecimal nonSummerKWhRate){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,plan,monthly,summerKWhRate,nonSummerKWhRate};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id=? ")
		.append("and e.status.typeCode=? ")
		.append("and e.plan.typeCode=? ")
		.append("and e.monthlyRate =? ")
		.append("and e.summerKWhRate =? ")
		.append("and e.nonSummerKWhRate =? ")
		.append("and e.kWRate IS NOT NULL ")
		.append("GROUP BY e.kWRate ")
		.append("ORDER BY e.kWRate ASC ");
		return super.find(EspRateCode.class, query.toString(), params);
	}
	
	//search SEASONAL rateCode
	public EspRateCode findSeasonalERateCode(Long utilityID, String plan,BigDecimal monthly,BigDecimal summerKWhRate,BigDecimal nonSummerKWhRate,BigDecimal kWRate){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,plan,monthly,summerKWhRate,nonSummerKWhRate,kWRate};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id=? ")
		.append("and e.status.typeCode=? ")
		.append("and e.plan.typeCode=? ")
		.append("and e.monthlyRate =? ")
		.append("and e.summerKWhRate =? ")
		.append("and e.nonSummerKWhRate =? ")
		.append("and e.kWRate =? ")
		.append("and e.rateCode IS NOT NULL ")
		.append("GROUP BY e.rateCode ")
		.append("ORDER BY e.rateCode ASC ");
		return super.findFirst(EspRateCode.class, query.toString(), params);
	}
	//search Discount
	public List<EspRateCode> findListDiscount(Long utilityID, String plan){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,plan};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id=? ")
		.append("and e.status.typeCode=? ")
		.append("and e.plan.typeCode=? ")
		.append("and e.discountAmount IS NOT NULL ")
		.append("GROUP BY e.discountAmount ")
		.append("ORDER BY e.discountAmount ASC ");
		return super.find(EspRateCode.class, query.toString(), params);
	}
	//search Discount rateCode
	public EspRateCode findDiscountERateCode(Long utilityID, String plan,BigDecimal discountAmount){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,plan,discountAmount};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id =? ")
		.append("and e.status.typeCode =? ")
		.append("and e.plan.typeCode =? ")
		.append("and e.discountAmount =? ")
		.append("and e.rateCode IS NOT NULL ")
		.append("GROUP BY e.rateCode ")
		.append("ORDER BY e.rateCode ASC ");
		return super.findFirst(EspRateCode.class, query.toString(), params);
	}
	
	public EspRateCode findEspRateCode(Long utilityID,String rateCode){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,rateCode};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id =? ")
		.append("and e.status.typeCode =? ")
		.append("and e.rateCode =? ")
		.append("ORDER BY id ");
		return super.findFirst(EspRateCode.class, query.toString(), params);
	}
	
	//search Standard rateCode MostNear
	public EspRateCode findStandardERateCodeMostNear(Long utilityID, String plan,BigDecimal monthly,BigDecimal kWhRate,BigDecimal kWRate){
		Object[] params = {utilityID,Constants.ESP_RATE_CODE_STATUS_ACTIVE,plan,monthly,kWhRate,kWRate};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id=? ")
		.append("and e.status.typeCode=? ")
		.append("and e.plan.typeCode=? ")
		.append("and e.monthlyRate =? ")
		.append("and e.kWhRate <=? ")
		.append("and e.kWRate =? ")
		.append("and e.rateCode IS NOT NULL ")
		.append("GROUP BY e.rateCode ")
		.append("ORDER BY e.rateCode ASC ");
		logger.info(query.toString());
		return super.findFirst(EspRateCode.class, query.toString(), params);
	}
	
	//search rateCode MostNear By Price(kWhRate)
	public EspRateCode findEspRateCodeByPrice(Long utilityId, BigDecimal price){
		Object[] params = {utilityId, Constants.ESP_RATE_CODE_STATUS_ACTIVE, price};
		StringBuilder query = new StringBuilder();
		query.append("FROM EspRateCode e ")
		.append("where e.utility.id =? ")
		.append("and e.status.typeCode =? ")
		.append("and e.kWhRate <=? ")
		.append("ORDER BY id ");
		return super.findFirst(EspRateCode.class, query.toString(), params);
	}	
}
